-- LG SubType attr change class (run)
<g>
-- not support system and ml attributes (exception is missing)
create procedure dbo.ChangeAttrClass(@AttrIC bigint, @AttrU int, @OClassIC bigint, @OClassU int, @NClassIC bigint, @NClassU int) as
begin
  if exists(select * from a103u0 where (ic_a = @OClassIC) and (u_a = @OClassU) and (ic_b = @AttrIC) and (u_b = @AttrU))
  begin
	declare @attrname nvarchar(50)
	set @attrname = 'a' + convert(nvarchar(30),@AttrIC) + 'u' + convert(nvarchar(20),@AttrU)
	declare @oclassname nvarchar(50)
	set @oclassname = 'a' + convert(nvarchar(30),@OClassIC) + 'u' + convert(nvarchar(20),@OClassU)
	declare @nclassname nvarchar(50)
	set @nclassname = 'a' + convert(nvarchar(30),@NClassIC) + 'u' + convert(nvarchar(20),@NClassU)

	declare @attrtype smallint
	declare @attrprec smallint
	declare @attrcollation nvarchar(50)
	select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @attrname

	declare @attrtypename nvarchar(100)
	if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
	if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
    if (@attrtype = 48) set @attrtypename = 'tinyint' else 
	if (@attrtype = 52) set @attrtypename = 'smallint' else
	if (@attrtype = 56) set @attrtypename = 'integer' else
	if (@attrtype = 127) set @attrtypename = 'bigint' else
	if (@attrtype = 62) set @attrtypename = 'float' else
	if (@attrtype = 61) set @attrtypename = 'datetime' else
	if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

	update a103u0 set ic_a = @NClassIC, u_a = @NClassU where ic_b = @AttrIC and u_b = @AttrU

	if (@attrcollation is not null) 
	  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
	  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename)
    exec('update a set ' + @attrname + ' = b.' + @attrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
	if exists(select * from sysindexes where name = 'i_' + @attrname)
    begin
      exec('drop index ' + @oclassname + '.i_' + @attrname)
	  exec('create index i_' + @attrname + ' on ' + @nclassname + '(' + @attrname + ')')
    end
    exec('alter table ' + @oclassname + ' drop column ' + @attrname)  
  end
end
</g>

<g>
exec ChangeAttrClass 328,0,24,0,22,0;
</g>

<g>
drop procedure dbo.ChangeAttrClass;
</g>